package mssql;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;

public class Connection {

	public static void populateEmmaImages(String path_images)
			throws IOException, ClassNotFoundException, SQLException {

		String connectionUrl = "jdbc:sqlserver://utwks10760.ewi.utwente.nl\\SQLEXPRESS;Database=EmmaLibrary;user=puppy;password=puppyir";// +
		// "databaseName=HelloWorld;integratedSecurity=true;";
		// Declare the JDBC objects.
		java.sql.Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

	//	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	//	con = DriverManager.getConnection(connectionUrl);
		
		
		String files;
		File folder = new File(path_images);
		File[] listOfFiles = folder.listFiles();

		for (int i = 0; i < listOfFiles.length; i++) {

			if (listOfFiles[i].isFile()) {
				files = listOfFiles[i].getName();
				String without_ext = files.substring(0, files.lastIndexOf("."));
				System.out.println(files);
				System.out.println(without_ext);

				// createFile

				InputStream stream = new FileInputStream(listOfFiles[i]
						.getAbsolutePath());
				
				
				//byte[] b = new byte[(int) listOfFiles[i].length()];
				//stream.read(b);

				// create statement to add the following info

				// id , file name, binary file data

				// Build the insert statement
				// Create a parameterized query

				try {
					// Establish the connection.
					
					Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
					con = DriverManager.getConnection(connectionUrl);
					// Create and execute an SQL statement that returns some
					// data.
					 String Query = ("insert emmaboekenafbeenldigen VALUES (?,?,?)");
					 PreparedStatement pstmt = con.prepareStatement(Query);
					 pstmt.setInt(2, Integer.valueOf(without_ext));
					 pstmt.setString(3, files);
					 pstmt.setBinaryStream(1, stream,listOfFiles[i].length()); //method to insert a stream of bytes

					 //execute the insert statement
					 pstmt.executeUpdate(); 
					 
					 System.out.println("Inserting "+ "\t" + files);
					
				
				}

				// Handle any errors that may have occurred.
				catch (Exception e) {
					e.printStackTrace();
				}

				finally {
					if (rs != null)
						try {
							rs.close();
						} catch (Exception e) {
						}
					if (stmt != null)
						try {
							stmt.close();
						} catch (Exception e) {
						}
					if (con != null)
						try {
							con.close();
						} catch (Exception e) {
						}
				}
			}

		}

	}
	
	
	public static void alterTable(String query){
		
		
	}
	
	 public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

		 String path ="/home/sergio/Dropbox/puppy_emma/cover";
		 populateEmmaImages(path);
		 
		/* // Create a variable for the connection string.
	        String connectionUrl = "jdbc:sqlserver://utwks10760.ewi.utwente.nl\\SQLEXPRESS;Database=EmmaLibrary;user=puppy;password=puppyir";// +
	            //"databaseName=HelloWorld;integratedSecurity=true;";

	        // Declare the JDBC objects.
	        java.sql.Connection con = null;
	        Statement stmt = null;
	        ResultSet rs = null;

	            try {
	                // Establish the connection.
	                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	                    con = DriverManager.getConnection(connectionUrl);

	                    // Create and execute an SQL statement that returns some data.
	                    String SQL = "SELECT TOP 10 * FROM emmakinderboeken";
	                    stmt = con.createStatement();
	                    rs = stmt.executeQuery(SQL);
	                    
	                    // Iterate through the data in the result set and display it.
	                    while (rs.next()) {
	                        System.out.println(rs.getString(1) + " " + rs.getString(3));
	                    }
	            }

	        // Handle any errors that may have occurred.
	        catch (Exception e) {
	            e.printStackTrace();
	        }

	        finally {
	            if (rs != null) try { rs.close(); } catch(Exception e) {}
	                if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	                if (con != null) try { con.close(); } catch(Exception e) {}
	        }
	    */
	 }
}
